--EXEC sprocCaseProfileIndexSelectCaseCount '01-Aug-2014', '31-Aug-2014'

ALTER PROCEDURE sprocCaseProfileIndexSelectCaseCount
(
	@StartDate datetime = NULL,
	@EndDate datetime = NULL
)
AS
--Declare @StartDate datetime
--Declare @EndDate datetime

--SET @StartDate = '1-Aug-2014'
--SET @EndDate = '31-Aug-2014'
	IF @StartDate IS NULL SET @StartDate = GETDATE()
	IF @EndDate IS NULL SET @EndDate = GETDATE()

	SET @EndDate = @EndDate + 1

	DECLARE @CutOffDateTime DATETIME,
		@StrDate VARCHAR(30),
		@StrTime VARCHAR(30)

	Declare @CutOffDate DateTime
	Declare @CutOffTime Time
	Declare @TempDate varchar(30)

	SET @CutOffDate = @StartDate
	SET @CutOffTime = '6:00 PM'
	SET @TempDate = CONVERT(VARCHAR, @CutOffDate, 101);
	SET @StrTime = CONVERT(VARCHAR, @CutOffTime, 108);
	SET @StrDate = (@TempDate + ' ' + @StrTime);
	SET @CutOffDateTime = CONVERT(DATETIME,@StrDate,121);
	SET @StartDate = @CutOffDateTime
	
	SET @StartDate = dbo.funcGetLastWorkingDateTime(@StartDate)
	SET @StartDate = DateAdd(millisecond, 2, @StartDate)

	SET @CutOffDate = @EndDate
	SET @CutOffTime = '6:00 PM'
	SET @TempDate = CONVERT(VARCHAR, @CutOffDate, 101);
	SET @StrTime = CONVERT(VARCHAR, @CutOffTime, 108);
	SET @StrDate = (@TempDate + ' ' + @StrTime);
	SET @CutOffDateTime = CONVERT(DATETIME,@StrDate,121);
	SET @EndDate = @CutOffDateTime
	
	SET @EndDate = dbo.funcGetLastWorkingDateTime(@EndDate)
	
	select @StartDate StartDate, @EndDate EndDate

	Declare @tblCase table
	(
		CaseSource varchar(20),
		ReceivedCount int,
		OpenCount int,
		PendingCount int,
		ClosedCount int
	)

	
	--INSERT INTO @tblCase Values('Direct Email', 0, 0, 0)

	Declare @ReceivedCount int
	Declare @OpenCount int
	Declare @PendingCount int
	Declare @ClosedCount int

	Select @ReceivedCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 1 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @OpenCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 1 And
			CaseStatus = 0 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @PendingCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 1 And
			CaseStatus = 1 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @ClosedCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 1 And
			CaseStatus = 100 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	INSERT INTO @tblCase Values('AskACRA', @ReceivedCount, @OpenCount, @PendingCount, @ClosedCount)


	Select @ReceivedCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 2 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @OpenCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 2 And
			CaseStatus = 0 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @PendingCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 2 And
			CaseStatus = 1 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	Select @ClosedCount = Count(CaseProfileIndex) from tblCaseProfileIndex
	Where	CaseSourceID = 2 And
			CaseStatus = 100 And 
			IsReplyCase = 0 And 
			CreatedDate Between @StartDate And @EndDate

	INSERT INTO @tblCase Values('Direct Email', @ReceivedCount, @OpenCount, @PendingCount, @ClosedCount)

	Select * from @tblCase